#include <QXmlStreamWriter>
#include <QXmlAttributes>
#include <QDateTime>
#include "sqlite3.h"
#include "DataModel.h"
#include "DataObject.h"
#include "Database.h"
#include "scImpl.h"

#define FCT "Database::Database"
Database::Database(const QString dbFile) :
	db(0), dbFile(dbFile), sqRc(0), initOk(false)
{
	sqRc=sqlite3_open(dbFile.toStdString().c_str(), &db);
	if(sqRc!=SQLITE_OK)
		{handleError("sqlite3_open", NULL, false); return;}
	initOk=true;
	return;
}

#undef FCT
#define FCT "Database::~Database"
Database::~Database()
{
	if(db)
		sqlite3_close(db);
}

#undef FCT
#define FCT "Database::errorString"
QString Database::errorString() const
{
	return QString(db?sqlite3_errmsg(db):"");
}

#undef FCT
#define FCT "Database::selectPragma"
bool Database::selectPragma(QXmlStreamWriter * writer, int * versMaj, int * versMin, int * versBld)
{
	if(!initOk)
		return false;
	bool testResult(false);
	sqlite3_stmt *stmt(0);
	const char *tail(0);
	QString sql("SELECT tag, value FROM m_pragma");
	sqRc=sqlite3_prepare_v2(db, sql.toUtf8().data(), sql.length(), &stmt, &tail);
	if(sqRc!=SQLITE_OK)
		handleError("sqlite3_prepare_v2", stmt);
	for(sqRc=sqlite3_step(stmt); true; sqRc=sqlite3_step(stmt))
	{
		if(sqRc==SQLITE_DONE)
			break;
		if(sqRc==SQLITE_ROW)
		{
			ctrc(Trc::LOOP,APP,FCT)<<"key: >"<<sqlite3_column_text(stmt, 0)<<"< value: >"<<sqlite3_column_text(stmt, 1)<<"<"<<endl;
			// UTF8 conversion yields empty fields...
			//key.fromUtf8((const char*)sqlite3_column_text(stmt, 0));
			QString key((const char*)sqlite3_column_text(stmt, 0));
			QString value((const char*)sqlite3_column_text(stmt, 1));
			writer->writeEmptyElement(QString("metaConnAttr"));
			writer->writeAttribute(QString("key"), key);
			writer->writeAttribute(QString("value"), value);
			if(key=="magic" && value=="sybil-app")
				testResult=true;
			else if(key=="version major")
				*versMaj=sqlite3_column_int(stmt, 1);
			else if(key=="version minor")
				*versMin=sqlite3_column_int(stmt, 1);
			else if(key=="version build")
				*versBld=sqlite3_column_int(stmt, 1);
			continue;
		}
		handleError("sqlite3_step", stmt);
	}
	sqRc=sqlite3_finalize(stmt);
	if(sqRc!=SQLITE_OK)
		handleError("sqlite3_finalize");
	return testResult;
}

#undef FCT
#define FCT "Database::selectBoot"
void Database::selectBoot(const Request & request, QXmlStreamWriter * writer)
{
	if(!initOk)
		return;
	if(request.rtype!=REQU_READ || request.stype!=SCOPE_TAKEALL)
		throw new AppException(FCT ": boot request must be whole-table-read", true);
	sqlite3_stmt *stmt(0);
	const char *tail(0);
	unsigned int maxCol(0);
	QString sql;
	request.toXmlEntityList(writer, true);
	request.toXmlScope(writer, false);
	if(request.entity==EID_M_ENTITIES)
	{
		sql="SELECT * FROM m_entities";
		maxCol=6;
	}
	else if(request.entity==EID_M_ATTRS)
	{
		sql="SELECT * FROM m_entity_attrs";
		maxCol=7;
	}
	else
	{
		ostringstream os;
		os <<FCT<<": unknown entity <"<<request.entity<<">";
		throw new AppException(os.str().c_str(), true);
	}
	sqRc=sqlite3_prepare_v2(db, sql.toUtf8().data(), sql.length(), &stmt, &tail);
	if(sqRc!=SQLITE_OK)
		handleError("sqlite3_prepare_v2(b)", stmt);
	for(sqRc=sqlite3_step(stmt); true; sqRc=sqlite3_step(stmt))
	{
		if(sqRc==SQLITE_DONE)
			break;
		if(sqRc==SQLITE_ROW)
		{
			writer->writeStartElement(QString("entity"));
			for(unsigned int colIdx(0); colIdx<maxCol; ++colIdx)
				writer->writeAttribute(QString("_")+QString::number(colIdx), QString((const char*)sqlite3_column_text(stmt, colIdx)));
			writer->writeEndElement(); // row
			continue;
		}
		handleError("sqlite3_step(b)", stmt);
	}
	request.toXmlEntityList(writer, false);
	sqRc=sqlite3_finalize(stmt);
	if(sqRc!=SQLITE_OK)
		handleError("sqlite3_finalize(b)");
}

#undef FCT
#define FCT "Database::select"
void Database::select(const Request & request, QXmlStreamWriter * writer, RequestDescriptor * depRequests)
{
	sqlite3_stmt *stmt(0);
	const char *tail(0);
	const DataTable & dt(DataModel::get()->getTable(request.entity));
	if(writer)
	{
		request.toXmlEntityList(writer, true);
		request.toXmlScope(writer, false);
	}
	const ColDescs & attrs(dt.getPayload().attrs);
	QString sql("SELECT ");
	sql+=dt.getAttrList()+" FROM "+dt.getTechNameQ();
	string errText;
	switch(request.stype)
	{
	case SCOPE_TAKEALL:
		break;
	case SCOPE_TS:
		if(dt.getDomain()==DOMAIN_DATATL)
			sql+=QString(" WHERE ")+sqltimeConstr(request.deadline);
		//else if(tblIt->second.domain==DOMAIN_DATAVS))
		else
			errText="SCOPE_TS";
		break;
	case SCOPE_PK:
	{
		sql+=QString(" WHERE ")+sqlPkList(QString("pk"), request.pks);
		break;
	}
	case SCOPE_IK:
	{
		if(dt.getDomain()!=DOMAIN_DATATL && dt.getDomain()!=DOMAIN_DATAVS)
			errText="SCOPE_IK";
		sql+=QString(" WHERE ")+sqlPkList(QString("ik"), request.pks);
		break;
	}
	case SCOPE_IKTS:
	{
		if(dt.getDomain()!=DOMAIN_DATATL && dt.getDomain()!=DOMAIN_DATAVS)
			errText="SCOPE_IKTS";
		sql+=QString(" WHERE ")+sqlPkList(QString("ik"), request.pks)+" AND "+sqltimeConstr(request.deadline);
		break;
	}
	case SCOPE_UNDEF:
		throw new AppException(FCT ": SCOPE_UNDEF is not a valid scope", true);
	}
	ctrc(Trc::WORK,APP,FCT)<<sql.toStdString().c_str()<<(writer?" (to XML)":" (DUR_SRV)")<<endl;
	if(!errText.empty())
	{
		ostringstream os;
		os <<FCT<<" - invalid use of "<<errText.c_str()<<" on table "<<request.entity;
		throw new AppException(os.str().c_str(), true);
	}
	sqRc=sqlite3_prepare_v2(db, sql.toUtf8().data(), sql.length(), &stmt, &tail);
	if(sqRc!=SQLITE_OK)
		handleError("sqlite3_prepare_v2(s)", stmt);
	for(sqRc=sqlite3_step(stmt); true; sqRc=sqlite3_step(stmt))
	{
		if(sqRc==SQLITE_DONE)
			break;
		if(sqRc==SQLITE_ROW)
		{
			QXmlAttributes xmlData;
			if(writer)
				writer->writeStartElement(QString("entity"));
			for(unsigned int colIdx(0); colIdx<attrs.size(); ++colIdx)
			{
				if(request.followFk && depRequests &&
					(attrs.at(colIdx).funct==AFUNCT_FPK || attrs.at(colIdx).funct==AFUNCT_FIK) &&
					attrs.at(colIdx).target!=request.entity) // suppress loop-back FKs
				{
					PkType depEnt(attrs.at(colIdx).target);
					//ctrc(Trc::LOOP,APP,FCT)<<"follow to "<<depEnt<<" xk "<<sqlite3_column_text(stmt, colIdx)<<endl;
					// TODO feature - cross-domain referencing
					if(attrs.at(colIdx).funct==AFUNCT_FIK)
					{
						if(request.deadline)
							depRequests->include(Request(depEnt, sqlite3_column_int(stmt, colIdx), request.deadline, false));
						else
						{
							if(dt.getDomain()==DOMAIN_DATATL)
								depRequests->include(Request(depEnt, sqlite3_column_int(stmt, colIdx), sqlite3_column_int(stmt, DB_COL_IKS), false));
							else
								depRequests->include(Request(depEnt, sqlite3_column_int(stmt, colIdx), DataModel::get()->getDeadlineUTC(), false));
						}
					}
					else
						depRequests->include(Request(depEnt, sqlite3_column_int(stmt, colIdx), false, SCOPE_PK));
				}
				/*int type(sqlite3_column_type(stmt, colIdx));
				if(type==SQLITE_TEXT || type==SQLITE_BLOB)
				{
					writer->writeStartElement(it->techName);
					writer->writeCharacters(QString((const char*)sqlite3_column_text(stmt, rowIdx)));
					writer->writeEndElement();
				}
				else */
				if(attrs.at(colIdx).dtype==ADTYPE_DATIM)
				{
					QDateTime dt(QDateTime::fromTime_t(sqlite3_column_int(stmt, colIdx)).toUTC());
					if(writer)
						writer->writeAttribute(QString("_")+QString::number(colIdx), dt.toString(QString(DATETIME_TECH)));
					else
						xmlData.append(QString("_")+QString::number(colIdx), QString(), QString(), dt.toString(QString(DATETIME_TECH)));
				}
				else
					if(writer)
						writer->writeAttribute(QString("_")+QString::number(colIdx), QString((const char*)sqlite3_column_text(stmt, colIdx)));
					else
						xmlData.append(QString("_")+QString::number(colIdx), QString(), QString(), QString((const char*)sqlite3_column_text(stmt, colIdx)));
			}
			if(writer)
				writer->writeEndElement(); // row
			else
				dt.getPayload().modifyData(xmlData, DUR_SRV);
			continue;
		}
		handleError("sqlite3_step(s)", stmt);
	}
	if(writer)
		request.toXmlEntityList(writer, false);
	sqRc=sqlite3_finalize(stmt);
	if(sqRc!=SQLITE_OK)
		handleError("sqlite3_finalize(s)");
}


#undef FCT
#define FCT "Database::update"
void Database::update(QXmlStreamWriter * writer, const Request & request)
{
	if(!initOk)
		return;
	sqlite3_stmt *stmt(0);
	const char *tail(0);
	const DataTable & dt(DataModel::get()->getTable(request.entity));
	dt.getPayload().getDos(DUR_SRV).clear();
	request.toXmlEntityList(writer, true);
	//request.toXmlScope(writer, false); update is scope-free
	for(Pks::const_iterator it(request.pks.begin()); it!=request.pks.end(); ++it)
	{
		Request checkRq(request.entity, *it, false, SCOPE_PK);
		select(checkRq, 0, 0);
		const DataObject * cliDo(dt.getByPk(*it, DUR_CLI));
		const DataObject * srvDo(dt.getByPk(*it, DUR_SRV));
		if(!cliDo)
			throw new AppException(FCT ": don't know what to update!", true);
		if(!srvDo)
			throw new AppException(FCT ": update should be insert!", true);
		DataObject_ & cliDo_(cliDo->getPayload());
		DataObject_ & srvDo_(srvDo->getPayload());
		if(cliDo_.at(DB_COL_LUT).getQDateTime()!=srvDo_.at(DB_COL_LUT).getQDateTime())
		{
			// srvDo should never be older than cliDo...
			ctrc(Trc::WORK,APP,FCT)<<"update conflict on "<<request.entity<<"/"<<*it<<": srv-tst "<<srvDo_.at(DB_COL_LUT).getQDateTime()<<" client-tst "<<cliDo_.at(DB_COL_LUT).getQDateTime()<<endl;
			writer->writeStartElement(QString("entity"));
			srvDo_.toXml(writer);
			writer->writeAttribute(QString("reason"), QString("update_conflict"));
			writer->writeEndElement(); // row
			continue;
		}
		if(dt.getDomain()==DOMAIN_DATATL &&
			(cliDo_.at(DB_COL_IKS).getQDateTime()!=srvDo_.at(DB_COL_IKS).getQDateTime() ||
			 cliDo_.at(DB_COL_IKS2).getQDateTime()!=srvDo_.at(DB_COL_IKS2).getQDateTime()))
		{ // check for IKS-IKS_ mismatch (split entry, adapt neighbor, create gap, ...)
			writer->writeStartElement(QString("entity_group")); // maybe it stays empty...
			Request checkRq(request.entity, srvDo->getIk(), false, SCOPE_IK);
			select(checkRq, 0, 0);
			bool iksOk(true);
			DOsCIkIdx & ikidx(dt.getPayload().sdos.get<DataObject::IkTag>());
			DOsCIkIdx::const_iterator end(ikidx.upper_bound(srvDo->getIk()));
			for(DOsCIkIdx::const_iterator ikit(ikidx.lower_bound(srvDo->getIk())); ikit!=end; ++ikit)
			{
				if(ikit->getPk()==srvDo->getPk())
					continue; // don't check against itself
				if(ikit->getIks2()<=cliDo->getIks() ||
					ikit->getIks()>=cliDo->getIks2()) // assuming well ordered iks<iks2
				{
					ctrc(Trc::LOOP,APP,FCT)<<"requested ("<<cliDo->getIks()<<","<<cliDo->getIks2()<<"] OK with ("<<ikit->getIks()<<","<<ikit->getIks2()<<"]"<<endl;
					continue; // not a conflict
				}
				ctrc(Trc::WORK,APP,FCT)<<"requested ("<<cliDo->getIks()<<","<<cliDo->getIks2()<<"] conflicts with ("<<ikit->getIks()<<","<<ikit->getIks2()<<"]"<<endl;
				writer->writeStartElement(QString("entity"));
				//ikit->getPayload().toXml(writer); // Why should we write the conflicting dataset?
				srvDo->getPayload().toXml(writer);
				writer->writeAttribute(QString("reason"), QString("timeline_conflict"));
				writer->writeEndElement(); // row
				iksOk=false;
			}
			writer->writeEndElement(); // entity_group
			if(!iksOk)
				continue;
		}
		cliDo_.at(DB_COL_LUT)=QDateTime::currentDateTime().toUTC();
		writer->writeStartElement(QString("entity"));
		writer->writeAttribute(QString("_0"), cliDo_.at(DB_COL_PK).getAsQString(false));
		writer->writeAttribute(QString("_1"), cliDo_.at(DB_COL_LUT).getAsQString(false));
		writer->writeEndElement(); // row
		QString sql("UPDATE "); // transaction is created implicitly
		sql+=dt.getTechNameQ()+" SET "+dt.getAttrValueList(*it)+" WHERE pk="+QString::number(*it);
		ctrc(Trc::WORK,APP,FCT)<<sql.toStdString().c_str()<<endl;
		sqRc=sqlite3_prepare_v2(db, sql.toUtf8().data(), sql.length(), &stmt, &tail);
		if(sqRc!=SQLITE_OK)
			handleError("sqlite3_prepare_v2(u)", stmt);
		for(sqRc=sqlite3_step(stmt); true; sqRc=sqlite3_step(stmt))
		{
			if(sqRc==SQLITE_DONE)
				break;
			if(sqRc==SQLITE_ROW)
				continue;
			handleError("sqlite3_step(u)", stmt);
		}
		sqRc=sqlite3_finalize(stmt);
		if(sqRc!=SQLITE_OK)
			handleError("sqlite3_finalize(u)");
	}
	request.toXmlEntityList(writer, false);
}

#undef FCT
#define FCT "Database::handleError"
void Database::handleError(const char * sqliteCall, sqlite3_stmt *statement, bool useExeption)
{
	if(statement)
		sqlite3_finalize(statement);
	ostringstream os;
	os <<sqliteCall<<" (code "<<sqlite3_errcode(db)<<" msg "<<sqlite3_errmsg(db)<<")";
	if(useExeption)
		throw DwkUtil::inst()->createException(APP, EXC_LOCALCONFAILED, os.str().c_str(), this);
	else
		ctrc(Trc::SETUP,APP,FCT)<<"EXC_LOCALCONFAILED: "<<os.str().c_str()<<endl;
}

#undef FCT
#define FCT "Database::sqlPkList"
QString Database::sqlPkList(const QString & key, const Pks & pks) const
{
	QString res(" ( ");
	bool follow(false);
	for(Pks::const_iterator it(pks.begin()); it!=pks.end(); ++it)
	{
		if(follow)
			res+=" OR ";
		else
			follow=true;
		res+=key+"="+QString::number(*it);
	}
	res+=" )";
	return res;
}

#undef FCT
#define FCT "Database::sqltimeConstr"
QString Database::sqltimeConstr(TimeType deadline) const
{
	QString res(" ( iks <= "), tm(QString::number(deadline));
	res+=tm+" AND iks2 > "+tm+" )";
	return res;
}
